---
layout: documentation
title: Malloy Documentation
footer: /generated/footers/examples/faa.html
---
The follow examples all run against the model at the bottom of this page OR you can find the source code here .
Where can you fly from SJC? For each destination; Which carriers? How long have they been flying there?
Are they ontime?
query : flights - > airport_dashboard {where : origin. code: 'SJC' }
code
destination
flight_count
carriers_by_month
routes_map
delay_by_hour_of_day
LAX
LOS ANGELES INTL
58,815
2000 2002 2004 dep_month 0 200 400 flight_count American American Eagle Sky West Southwest United nickname
58,815 flight_count
0 5 10 15 20 dep_hour 0 20 40 flight_count 31 489 delay
SAN
SAN DIEGO INTL-LINDBERGH FLD
34,702
2000 2002 2004 dep_month 0 100 200 300 flight_count American American Eagle Continental Southwest nickname
34,702 flight_count
0 5 10 15 20 dep_hour 0 10 20 30 flight_count 31 730 delay
SNA
JOHN WAYNE AIRPORT-ORANGE COUNTY
30,907
2000 2002 2004 dep_month 0 100 200 flight_count American American Eagle Southwest nickname
30,907 flight_count
0 5 10 15 20 dep_hour 0 10 20 flight_count 200 400 600 delay
LAS
MC CARRAN INTL
25,825
2000 2002 2004 dep_month 0 100 200 flight_count America West American Southwest nickname
25,825 flight_count
0 5 10 15 20 dep_hour 0 10 20 flight_count 31 739 delay
SEA
SEATTLE-TACOMA INTL
24,864
2000 2002 2004 dep_month 0 100 200 300 flight_count Alaska American Hawaiian Southwest nickname
24,864 flight_count
0 5 10 15 20 dep_hour 0 10 20 flight_count 200 400 600 delay
PHX
PHOENIX SKY HARBOR INTL
24,310
2000 2002 2004 dep_month 0 100 200 flight_count America West American American Eagle Southwest nickname
24,310 flight_count
0 5 10 15 20 dep_hour 0 5 10 flight_count 31 577 delay
ORD
CHICAGO O'HARE INTL
21,997
2000 2002 2004 dep_month 0 100 200 flight_count American United nickname
21,997 flight_count
0 5 10 15 20 dep_hour 0 5 10 15 flight_count 31 534 delay
PDX
PORTLAND INTL
21,562
2000 2002 2004 dep_month 0 100 200 flight_count Alaska American Southwest nickname
21,562 flight_count
0 5 10 15 20 dep_hour 0 10 20 flight_count 31 769 delay
BUR
BURBANK-GLENDALE-PASADENA
17,557
2000 2002 2004 dep_month 0 100 200 flight_count Southwest nickname
17,557 flight_count
0 5 10 15 20 dep_hour 0 10 20 30 40 flight_count 100 200 300 delay
ONT
ONTARIO INTL
17,358
2000 2002 2004 dep_month 0 100 200 300 flight_count Sky West Southwest nickname
17,358 flight_count
0 5 10 15 20 dep_hour 0 10 20 flight_count 31 335 delay
Tell me everything about a carrier. How many destinations?, flights? hubs?
What kind of planes to they use? How many flights over time? What are
the major hubs? For each destionation, How many flights? Where can you? Have they been
flying there long? Increasing or decresing year by year? Any seasonality?
query : flights - > carrier_dashboard {where : carriers. nickname : 'Jetblue' }
destination_count
flight_count
by_manufacturer
by_month
hubs
origin_dashboard
33
267,963
0 20 40 60 aircraft_count AIRBUS AIRBUS INDUSTRIE EMBRAER null 1,151 161,208 flight_count
2003 2004 2005 dep_month 0 5,000 10,000 flight_count
hub
destination_count
JFK - JOHN F KENNEDY INTL
29
BOS - GENERAL EDWARD LAWRENCE LOGAN INTL
14
LGB - LONG BEACH /DAUGHERTY FIELD/
9
FLL - FORT LAUDERDALE/HOLLYWOOD INTL
7
OAK - METROPOLITAN OAKLAND INTL
7
IAD - WASHINGTON DULLES INTERNATIONAL
6
EWR - NEWARK INTL
6
PBI - PALM BEACH INTL
4
ONT - ONTARIO INTL
4
LAS - MC CARRAN INTL
3
origin
JOHN F KENNEDY INTL
destinations_by_month
2003 2004 2005 dep_month 0 200 400 flight_count null BOS - GENERAL EDWA… BQN - RAFAEL HERNA… BTV - BURLINGTON I… BUF - BUFFALO NIAG… BUR - BURBANK-GLEN… DEN - DENVER INTL EWR - NEWARK INTL FLL - FORT LAUDERD… LAS - MC CARRAN IN… LAX - LOS ANGELES … LGB - LONG BEACH /… MCO - ORLANDO INTL MSY - NEW ORLEANS … OAK - METROPOLITAN… ONT - ONTARIO INTL PBI - PALM BEACH I… PDX - PORTLAND INTL PHX - PHOENIX SKY … PSE - MERCEDITA ROC - GREATER ROCH… RSW - SOUTHWEST FL… SAN - SAN DIEGO IN… SEA - SEATTLE-TACO… SJC - SAN JOSE INT… SJU - LUIS MUNOZ M… SLC - SALT LAKE CI… SMF - SACRAMENTO I… SYR - SYRACUSE HAN… TPA - TAMPA INTL name
year_over_year
2 4 6 8 10 12 dep_month 0 1,000 2,000 3,000 4,000 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
origin
LONG BEACH /DAUGHERTY FIELD/
destinations_by_month
2003 2004 2005 dep_month 0 100 200 flight_count ATL - THE WILLIAM … BOS - GENERAL EDWA… FLL - FORT LAUDERD… IAD - WASHINGTON D… JFK - JOHN F KENNE… LAS - MC CARRAN IN… OAK - METROPOLITAN… ONT - ONTARIO INTL SLC - SALT LAKE CI… name
routes_map
2,000 4,000 6,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 200 400 600 800 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
origin
FORT LAUDERDALE/HOLLYWOOD INTL
destinations_by_month
2003 2004 2005 dep_month 0 200 400 flight_count BOS - GENERAL EDWA… BTV - BURLINGTON I… EWR - NEWARK INTL IAD - WASHINGTON D… JFK - JOHN F KENNE… LGA - LA GUARDIA LGB - LONG BEACH /… name
year_over_year
2 4 6 8 10 12 dep_month 0 500 1,000 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
origin
METROPOLITAN OAKLAND INTL
destinations_by_month
2003 2004 2005 dep_month 0 100 200 flight_count ATL - THE WILLIAM … BOS - GENERAL EDWA… IAD - WASHINGTON D… JFK - JOHN F KENNE… LAX - LOS ANGELES … LGB - LONG BEACH /… ONT - ONTARIO INTL name
routes_map
2,000 4,000 6,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 200 400 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
destinations_by_month
2003 2004 2005 dep_month 0 100 200 300 400 flight_count BOS - GENERAL EDWA… EWR - NEWARK INTL JFK - JOHN F KENNE… name
routes_map
2,000 4,000 6,000 8,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 200 400 600 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
origin
GENERAL EDWARD LAWRENCE LOGAN INTL
destinations_by_month
2004 April July October 2005 April July October dep_month 0 100 200 flight_count DEN - DENVER INTL FLL - FORT LAUDERD… JFK - JOHN F KENNE… LAS - MC CARRAN IN… LAX - LOS ANGELES … LGB - LONG BEACH /… MCO - ORLANDO INTL OAK - METROPOLITAN… ONT - ONTARIO INTL PBI - PALM BEACH I… RSW - SOUTHWEST FL… SEA - SEATTLE-TACO… SJC - SAN JOSE INT… TPA - TAMPA INTL name
routes_map
500 1,000 1,500 2,000 2,500 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 500 1,000 flight_count Dec 31, 2003 Dec 31, 2004 dep_year
origin
WASHINGTON DULLES INTERNATIONAL
destinations_by_month
2003 2004 2005 dep_month 0 50 100 flight_count FLL - FORT LAUDERD… JFK - JOHN F KENNE… LGB - LONG BEACH /… OAK - METROPOLITAN… SAN - SAN DIEGO IN… SMF - SACRAMENTO I… name
routes_map
1,000 2,000 3,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 100 200 300 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
destinations_by_month
2003 2004 2005 dep_month 0 100 200 flight_count BOS - GENERAL EDWA… EWR - NEWARK INTL JFK - JOHN F KENNE… name
routes_map
2,000 4,000 6,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 100 200 300 400 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
destinations_by_month
2003 2004 2005 dep_month 0 100 200 300 400 flight_count BOS - GENERAL EDWA… EWR - NEWARK INTL JFK - JOHN F KENNE… LGA - LA GUARDIA name
routes_map
2,000 4,000 6,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 200 400 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
destinations_by_month
2003 2004 2005 dep_month 0 50 100 150 200 flight_count BOS - GENERAL EDWA… JFK - JOHN F KENNE… LGB - LONG BEACH /… name
routes_map
1,000 2,000 3,000 4,000 flight_count
year_over_year
2 4 6 8 10 12 dep_month 0 100 200 300 flight_count Dec 31, 2002 Dec 31, 2004 dep_year
Suppose you wanted to build a website like Kayak. Let's assume that the data we have is
in the future instead ofthe past. The query below will fetch all the data needed
to render a Kayak page in a singe query.
query : flights - > kayak {
where : [
origin. code : 'SJC' ,
destination. code : 'LAX' | 'BUR' ,
dep_time : @2004-01-01
]
}
carriers
by_hour
flights
nickname
flight_count
Southwest
16
American Eagle
11
Sky West
6
dep_hour
flight_count
6
2
8
2
9
3
10
3
11
1
13
3
14
2
15
3
16
2
17
5
18
2
19
4
21
1
dep_minute
name
flight_num
origin_code
destination_code
manufacturer
model
2004-01-01 21:05
Southwest Airlines
2866
SJC
LAX
BOEING
737-317
2004-01-01 19:55
Sky West Airlines
6991
SJC
LAX
BOMBARDIER INC
CL-600-2B19
2004-01-01 19:54
Southwest Airlines
1437
SJC
LAX
BOEING
737-7H4
2004-01-01 19:40
Southwest Airlines
3353
SJC
BUR
BOEING
737-7H4
2004-01-01 19:20
American Eagle Airlines
3216
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 18:20
Southwest Airlines
2484
SJC
LAX
BOEING
737-3H4
2004-01-01 18:17
Southwest Airlines
565
SJC
BUR
BOEING
737-317
2004-01-01 17:51
American Eagle Airlines
3152
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 17:36
Sky West Airlines
6987
SJC
LAX
BOMBARDIER INC
CL-600-2B19
2004-01-01 17:30
Southwest Airlines
495
SJC
LAX
BOEING
737-3H4
2004-01-01 17:14
American Eagle Airlines
3206
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 17:00
Southwest Airlines
1664
SJC
LAX
BOEING
737-3H4
2004-01-01 16:35
Southwest Airlines
990
SJC
BUR
BOEING
737-3Q8
2004-01-01 16:10
Southwest Airlines
1383
SJC
LAX
BOEING
737-7H4
2004-01-01 15:32
Sky West Airlines
6980
SJC
LAX
BOMBARDIER INC
CL-600-2B19
2004-01-01 15:30
Southwest Airlines
624
SJC
LAX
BOEING
737-7H4
2004-01-01 15:13
American Eagle Airlines
3168
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 14:34
American Eagle Airlines
3160
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 14:02
American Eagle Airlines
3148
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 13:25
Southwest Airlines
197
SJC
BUR
BOEING
737-5H4
2004-01-01 13:17
Sky West Airlines
6948
SJC
LAX
BOMBARDIER INC
CL-600-2B19
2004-01-01 13:15
Southwest Airlines
2226
SJC
LAX
BOEING
737-3H4
2004-01-01 11:56
American Eagle Airlines
3198
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 10:35
Southwest Airlines
603
SJC
BUR
BOEING
737-3G7
2004-01-01 10:24
American Eagle Airlines
3200
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 10:15
Southwest Airlines
1593
SJC
LAX
BOEING
737-3Q8
2004-01-01 09:40
Southwest Airlines
1478
SJC
BUR
BOEING
737-5H4
2004-01-01 09:31
American Eagle Airlines
3196
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 09:30
Southwest Airlines
1323
SJC
LAX
BOEING
737-3H4
2004-01-01 08:52
Sky West Airlines
6946
SJC
LAX
BOMBARDIER INC
CL-600-2B19
2004-01-01 08:24
American Eagle Airlines
3194
SJC
LAX
EMBRAER
EMB-135KL
2004-01-01 06:34
Sky West Airlines
6993
SJC
LAX
BOMBARDIER INC
CL-600-2B19
2004-01-01 06:14
American Eagle Airlines
3192
SJC
LAX
EMBRAER
EMB-135KL
You can think of flight data as event data. The below is a classic map/reduce roll up of the filght data by carrier and day, plane and day, and individual events for each plane.
query : sessionize is {
group_by : flight_date is dep_time. day
group_by : carrier
aggregate : daily_flight_count is flight_count
nest : per_plane_data is {
top : 20
group_by : tail_num
aggregate : plane_flight_count is flight_count
nest : flight_legs is {
order_by : 2
group_by : [
tail_num
dep_minute is dep_time. minute
origin_code
dest_code is destination_code
dep_delay
arr_delay
]
}
}
} query : flights {where : [ carrier:'WN' , dep_time: @2002-03-03 ] } - > sessionize
flight_date
carrier
daily_flight_count
per_plane_data
[object Object]
WN
2,411
tail_num
plane_flight_count
flight_legs
N721WN
12
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N721WN
2002-03-03 06:30
HOU
AUS
0
1
N721WN
2002-03-03 07:35
AUS
DAL
0
-2
N721WN
2002-03-03 08:55
DAL
SAT
0
0
N721WN
2002-03-03 10:20
SAT
HOU
0
0
N721WN
2002-03-03 11:30
HOU
DAL
0
0
N721WN
2002-03-03 12:50
DAL
SAT
0
-5
N721WN
2002-03-03 14:15
SAT
HOU
5
0
N721WN
2002-03-03 15:20
HOU
SAT
0
4
N721WN
2002-03-03 16:34
SAT
HOU
4
-4
N721WN
2002-03-03 17:40
HOU
MSY
0
-2
N721WN
2002-03-03 19:10
MSY
DAL
5
10
N721WN
2002-03-03 20:55
DAL
AMA
5
2
N501SW
11
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N501SW
2002-03-03 09:25
AMA
DAL
0
-5
N501SW
2002-03-03 10:45
DAL
AUS
0
1
N501SW
2002-03-03 11:55
AUS
HRL
0
-4
N501SW
2002-03-03 13:20
HRL
HOU
0
0
N501SW
2002-03-03 15:00
HOU
DAL
60
52
N501SW
2002-03-03 16:25
DAL
AMA
70
70
N501SW
2002-03-03 17:58
AMA
DAL
78
75
N501SW
2002-03-03 19:20
DAL
HOU
80
84
N501SW
2002-03-03 20:44
HOU
HRL
79
85
N501SW
2002-03-03 21:59
HRL
HOU
74
72
N501SW
2002-03-03 23:00
HOU
DAL
60
68
N506SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N506SW
2002-03-03 09:16
TPA
MSY
6
10
N506SW
2002-03-03 10:40
MSY
HOU
20
21
N506SW
2002-03-03 12:15
HOU
HRL
30
40
N506SW
2002-03-03 13:41
HRL
AUS
46
34
N506SW
2002-03-03 14:51
AUS
DAL
21
20
N506SW
2002-03-03 16:06
DAL
TUL
16
8
N506SW
2002-03-03 17:21
TUL
DAL
6
1
N506SW
2002-03-03 18:45
DAL
LBB
15
10
N506SW
2002-03-03 20:05
LBB
DAL
5
-3
N506SW
2002-03-03 21:32
DAL
MSY
7
5
N712SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N712SW
2002-03-03 08:40
PBI
BNA
0
2
N712SW
2002-03-03 10:17
BNA
MDW
7
-2
N712SW
2002-03-03 12:05
MDW
DTW
10
5
N712SW
2002-03-03 14:33
DTW
MDW
13
15
N712SW
2002-03-03 15:10
MDW
STL
20
15
N712SW
2002-03-03 16:44
STL
HOU
24
10
N712SW
2002-03-03 19:20
HOU
DAL
20
15
N712SW
2002-03-03 20:37
DAL
OKC
22
22
N712SW
2002-03-03 21:40
OKC
STL
20
13
N712SW
2002-03-03 23:12
STL
MDW
12
6
N301SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N301SW
2002-03-03 06:30
ONT
LAS
0
-2
N301SW
2002-03-03 07:58
LAS
RNO
8
9
N301SW
2002-03-03 09:34
RNO
SJC
4
-2
N301SW
2002-03-03 10:55
SJC
ONT
5
-2
N301SW
2002-03-03 12:25
ONT
LAS
0
10
N301SW
2002-03-03 13:55
LAS
SAN
10
-5
N301SW
2002-03-03 15:10
SAN
SJC
0
-3
N301SW
2002-03-03 17:05
SJC
LAS
10
12
N301SW
2002-03-03 19:00
LAS
PHX
20
20
N301SW
2002-03-03 21:35
PHX
STL
20
18
N722WN
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N722WN
2002-03-03 09:00
MSY
HOU
0
-2
N722WN
2002-03-03 10:53
HOU
DAL
23
20
N722WN
2002-03-03 12:09
DAL
MAF
24
25
N722WN
2002-03-03 13:34
MAF
DAL
24
15
N722WN
2002-03-03 14:49
DAL
SAT
19
26
N722WN
2002-03-03 16:22
SAT
DAL
22
16
N722WN
2002-03-03 17:35
DAL
AUS
15
12
N722WN
2002-03-03 18:45
AUS
DAL
10
1
N722WN
2002-03-03 19:55
DAL
SAT
0
0
N722WN
2002-03-03 21:10
SAT
DAL
0
5
N311SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N311SW
2002-03-03 07:25
TUS
LAS
0
2
N311SW
2002-03-03 08:15
LAS
PHX
10
6
N311SW
2002-03-03 10:50
PHX
ABQ
20
8
N311SW
2002-03-03 12:15
ABQ
AMA
10
12
N311SW
2002-03-03 14:35
AMA
ABQ
15
3
N311SW
2002-03-03 14:40
ABQ
PHX
0
-14
N311SW
2002-03-03 16:10
PHX
SJC
50
55
N311SW
2002-03-03 17:53
SJC
LAX
68
53
N311SW
2002-03-03 19:26
LAX
PHX
56
44
N311SW
2002-03-03 22:14
PHX
ABQ
54
51
N322SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N322SW
2002-03-03 07:45
BDL
BWI
0
4
N322SW
2002-03-03 09:15
BWI
ALB
0
-8
N322SW
2002-03-03 10:45
ALB
BWI
0
0
N322SW
2002-03-03 12:25
BWI
SDF
5
10
N322SW
2002-03-03 14:45
SDF
STL
15
18
N322SW
2002-03-03 15:15
STL
TUL
15
10
N322SW
2002-03-03 16:59
TUL
PHX
14
20
N322SW
2002-03-03 19:14
PHX
BUR
29
20
N322SW
2002-03-03 19:52
BUR
OAK
17
11
N322SW
2002-03-03 21:38
OAK
SLC
28
22
N515SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N515SW
2002-03-03 08:05
OKC
DAL
0
5
N515SW
2002-03-03 09:50
DAL
LBB
0
5
N515SW
2002-03-03 11:22
LBB
AUS
12
10
N515SW
2002-03-03 12:35
AUS
HOU
0
4
N515SW
2002-03-03 14:02
HOU
MSY
12
10
N515SW
2002-03-03 15:48
MSY
MCO
33
25
N515SW
2002-03-03 18:40
MCO
FLL
25
16
N515SW
2002-03-03 19:50
FLL
MCO
10
15
N515SW
2002-03-03 21:10
MCO
MSY
10
5
N515SW
2002-03-03 22:15
MSY
HOU
5
13
N333SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N333SW
2002-03-03 08:10
FLL
MCO
0
-5
N333SW
2002-03-03 09:35
MCO
IND
0
-10
N333SW
2002-03-03 12:37
IND
MDW
7
4
N333SW
2002-03-03 13:00
MDW
BWI
20
21
N333SW
2002-03-03 16:10
BWI
ORF
55
60
N333SW
2002-03-03 17:20
ORF
BWI
60
48
N333SW
2002-03-03 18:30
BWI
ORF
50
58
N333SW
2002-03-03 19:45
ORF
MCO
60
67
N333SW
2002-03-03 22:20
MCO
BNA
65
52
N333SW
2002-03-03 23:26
BNA
MDW
46
43
N600WN
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N600WN
2002-03-03 08:00
ELP
DAL
0
-12
N600WN
2002-03-03 11:00
DAL
TUL
0
-1
N600WN
2002-03-03 12:10
TUL
DAL
0
-3
N600WN
2002-03-03 13:25
DAL
MAF
0
6
N600WN
2002-03-03 14:55
MAF
DAL
5
-1
N600WN
2002-03-03 16:19
DAL
SAT
4
0
N600WN
2002-03-03 17:40
SAT
HOU
0
-14
N600WN
2002-03-03 18:55
HOU
SAT
0
-3
N600WN
2002-03-03 20:10
SAT
LAS
0
-2
N600WN
2002-03-03 21:38
LAS
LAX
13
-4
N613SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N613SW
2002-03-03 07:25
STL
PHX
0
-28
N613SW
2002-03-03 10:30
PHX
LAX
0
-14
N613SW
2002-03-03 11:25
LAX
SJC
0
-5
N613SW
2002-03-03 13:12
SJC
LAS
12
8
N613SW
2002-03-03 14:56
LAS
ONT
11
3
N613SW
2002-03-03 16:22
ONT
LAS
17
13
N613SW
2002-03-03 17:57
LAS
RNO
37
40
N613SW
2002-03-03 19:40
RNO
LAS
40
38
N613SW
2002-03-03 21:24
LAS
BUR
49
45
N613SW
2002-03-03 22:47
BUR
LAS
52
52
N607SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N607SW
2002-03-03 06:50
MAF
HOU
0
-7
N607SW
2002-03-03 08:20
HOU
JAN
0
-8
N607SW
2002-03-03 09:50
JAN
BWI
0
-22
N607SW
2002-03-03 13:25
BWI
PVD
0
-7
N607SW
2002-03-03 14:51
PVD
BWI
126
126
N607SW
2002-03-03 16:45
BWI
ORF
135
146
N607SW
2002-03-03 17:56
ORF
JAX
141
148
N607SW
2002-03-03 20:15
JAX
ORF
150
120
N607SW
2002-03-03 21:47
ORF
BWI
112
95
N607SW
2002-03-03 23:04
BWI
ORF
114
120
N90SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N90SW
2002-03-03 10:00
LBB
DAL
0
-11
N90SW
2002-03-03 11:25
DAL
IAH
0
9
N90SW
2002-03-03 12:45
IAH
DAL
0
0
N90SW
2002-03-03 14:00
DAL
HOU
0
2
N90SW
2002-03-03 15:25
HOU
CRP
5
7
N90SW
2002-03-03 16:40
CRP
HOU
15
13
N90SW
2002-03-03 17:38
HOU
DAL
8
3
N90SW
2002-03-03 18:45
DAL
IAH
0
-5
N90SW
2002-03-03 20:05
IAH
DAL
0
-10
N90SW
2002-03-03 21:30
DAL
MAF
5
2
N664WN
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N664WN
2002-03-03 06:35
SEA
SMF
0
-10
N664WN
2002-03-03 08:45
SMF
ONT
0
-8
N664WN
2002-03-03 10:24
ONT
PHX
4
-3
N664WN
2002-03-03 13:10
PHX
SLC
15
14
N664WN
2002-03-03 15:09
SLC
BOI
14
5
N664WN
2002-03-03 16:30
BOI
PDX
10
6
N664WN
2002-03-03 17:10
PDX
OAK
15
1
N664WN
2002-03-03 19:05
OAK
LAX
5
-5
N664WN
2002-03-03 21:00
LAX
LAS
0
-6
N664WN
2002-03-03 22:30
LAS
PHX
0
-6
N365SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N365SW
2002-03-03 07:25
ALB
BWI
0
0
N365SW
2002-03-03 09:05
BWI
BNA
0
9
N365SW
2002-03-03 10:35
BNA
LAS
5
14
N365SW
2002-03-03 13:32
LAS
BUR
22
18
N365SW
2002-03-03 15:09
BUR
OAK
34
33
N365SW
2002-03-03 16:56
OAK
BUR
46
45
N365SW
2002-03-03 18:24
BUR
SMF
49
54
N365SW
2002-03-03 20:09
SMF
BUR
59
55
N365SW
2002-03-03 21:27
BUR
LAS
47
41
N365SW
2002-03-03 22:36
LAS
ABQ
36
34
N89SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N89SW
2002-03-03 08:00
DAL
HOU
0
-3
N89SW
2002-03-03 09:35
HOU
DAL
5
0
N89SW
2002-03-03 10:45
DAL
OKC
0
-2
N89SW
2002-03-03 11:50
OKC
DAL
0
-5
N89SW
2002-03-03 13:05
DAL
IAH
0
-8
N89SW
2002-03-03 14:20
IAH
DAL
0
-1
N89SW
2002-03-03 15:40
DAL
ABQ
0
0
N89SW
2002-03-03 16:55
ABQ
AMA
0
5
N89SW
2002-03-03 19:10
AMA
DAL
0
0
N89SW
2002-03-03 20:30
DAL
IAH
0
4
N93SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N93SW
2002-03-03 10:45
MAF
ABQ
0
2
N93SW
2002-03-03 11:24
ABQ
ELP
14
12
N93SW
2002-03-03 12:31
ELP
SAT
11
14
N93SW
2002-03-03 15:19
SAT
HOU
19
6
N93SW
2002-03-03 16:30
HOU
AUS
30
23
N93SW
2002-03-03 17:25
AUS
MAF
15
14
N93SW
2002-03-03 18:44
MAF
AUS
4
0
N93SW
2002-03-03 20:00
AUS
HOU
0
-11
N93SW
2002-03-03 21:14
HOU
AUS
9
4
N93SW
2002-03-03 22:10
AUS
ELP
0
5
N510SW
10
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N510SW
2002-03-03 07:45
LAX
RNO
0
-5
N510SW
2002-03-03 09:25
RNO
BOI
0
-3
N510SW
2002-03-03 11:55
BOI
GEG
0
-5
N510SW
2002-03-03 12:20
GEG
BOI
0
-2
N510SW
2002-03-03 14:40
BOI
RNO
0
-7
N510SW
2002-03-03 15:10
RNO
LAX
0
-7
N510SW
2002-03-03 17:05
LAX
TUS
10
2
N510SW
2002-03-03 19:40
TUS
LAX
0
-6
N510SW
2002-03-03 20:40
LAX
SJC
0
1
N510SW
2002-03-03 22:10
SJC
PDX
0
-4
N616SW
9
tail_num
dep_minute
origin_code
dest_code
dep_delay
arr_delay
N616SW
2002-03-03 07:34
STL
MDW
4
3
N616SW
2002-03-03 08:55
MDW
BWI
0
7
N616SW
2002-03-03 12:15
BWI
ALB
5
2
N616SW
2002-03-03 13:50
ALB
BWI
10
9
N616SW
2002-03-03 15:40
BWI
CLE
25
14
N616SW
2002-03-03 17:23
CLE
MDW
28
21
N616SW
2002-03-03 18:08
MDW
CMH
33
28
N616SW
2002-03-03 20:43
CMH
MDW
43
43
N616SW
2002-03-03 21:25
MDW
BWI
55
49
All of the queries above are executed against the following model:
explore : airports is table ( 'malloy-data.faa.airports' ) {
primary_key : code
dimension : name is concat ( code, ' - ' , full_name)
measure : airport_count is count ( )
}
explore : carriers is table ( 'malloy-data.faa.carriers' ) {
primary_key : code
measure : carrier_count is count ( )
}
explore : aircraft_models is table ( 'malloy-data.faa.aircraft_models' ) {
primary_key : aircraft_model_code
measure : aircraft_model_count is count ( )
}
explore : aircraft is table ( 'malloy-data.faa.aircraft' ) {
primary_key : tail_num
measure : aircraft_count is count ( )
join : aircraft_models on aircraft_model_code
}
explore : aircraft_facts is from (
table ( 'malloy-data.faa.flights' ) - > {
group_by : tail_num
aggregate : [
lifetime_flights is count ( )
lifetime_distance is distance. sum ( )
]
}
) {
primary_key : tail_num
dimension : lifetime_flights_bucketed is floor ( lifetime_flights/ 1000 ) * 1000
}
explore : flights is table ( 'malloy-data.faa.flights' ) {
primary_key : id2
rename : origin_code is origin
rename : destination_code is destination
join : carriers on carrier
join : origin is airports on origin_code
join : destination is airports on destination_code
join : aircraft on tail_num
join : aircraft_facts on tail_num
measure : [
flight_count is count ( )
total_distance is sum ( distance)
seats_for_sale is sum ( aircraft. aircraft_models. seats)
seats_owned is aircraft. sum ( aircraft. aircraft_models. seats)
]
query : measures is {
aggregate : [
flight_count
aircraft. aircraft_count
dest_count is destination. airport_count
origin_count is origin. airport_count
]
}
query : by_carrier is {
group_by : carriers. nickname
aggregate : flight_count
aggregate : destination_count is destination. count ( )
}
query : year_over_year is {
group_by : dep_month is month ( dep_time)
aggregate : flight_count
group_by : dep_year is dep_time. year
}
query : by_manufacturer is {
top : 5
group_by : aircraft. aircraft_models. manufacturer
aggregate : [ aircraft. aircraft_count, flight_count ]
}
query : delay_by_hour_of_day is {
where : dep_delay > 30
group_by : dep_hour is hour ( dep_time)
aggregate : flight_count
group_by : delay is FLOOR ( dep_delay) / 30 * 30
}
query : carriers_by_month is {
group_by : dep_month is dep_time. month
aggregate : flight_count
group_by : carriers. nickname
}
query : seats_by_distance is {
group_by : seats is floor ( aircraft. aircraft_models. seats/ 5 ) * 5
aggregate : flight_count
group_by : distance is floor ( distance/ 20 ) * 20
}
query : routes_map is {
group_by : [
origin. latitude
origin. longitude
latitude2 is destination. latitude
longitude2 is destination. longitude
]
aggregate : flight_count
}
query : destinations_by_month is {
group_by : dep_month is dep_time. month
aggregate : flight_count
group_by : destination. name
}
query : airport_dashboard is {
top : 10
group_by : code is destination_code
group_by : destination is destination. full_name
aggregate : flight_count
nest : [ carriers_by_month, routes_map, delay_by_hour_of_day]
}
query : plane_usage is {
order_by : 1 desc
where : aircraft. aircraft_count > 1
group_by : aircraft_facts. lifetime_flights_bucketed
aggregate : [ aircraft. aircraft_count, flight_count]
nest : [ by_manufacturer, by_carrier]
}
query : carrier_dashboard is {
aggregate : destination_count is destination. airport_count
aggregate : flight_count
nest : by_manufacturer
nest : by_month is {
group_by : dep_month is dep_time. month
aggregate : flight_count
}
nest : hubs is {
top : 10
where : destination. airport_count > 1
group_by : hub is origin. name
aggregate : destination_count is destination. airport_count
}
nest : origin_dashboard is {
top : 10
group_by : [
code is origin_code,
origin is origin. full_name,
origin. city
]
aggregate : flight_count
nest : [ destinations_by_month, routes_map, year_over_year]
}
}
query : detail is {
top : 30 by dep_time
project : [
id2, dep_time, tail_num, carrier, origin_code, destination_code, distance, aircraft. aircraft_model_code
]
}
query : kayak is {
nest : carriers is {
group_by : carriers. nickname
aggregate : flight_count
}
nest : by_hour is {
order_by : 1
group_by : dep_hour is hour ( dep_time)
aggregate : flight_count
}
nest : flights is {
group_by : [
dep_minute is dep_time. minute
carriers. name
flight_num
origin_code
destination_code
aircraft. aircraft_models. manufacturer
aircraft. aircraft_models. model
]
}
}
query : sessionize is {
group_by : flight_date is dep_time. day
group_by : carrier
aggregate : daily_flight_count is flight_count
nest : per_plane_data is {
top : 20
group_by : tail_num
aggregate : plane_flight_count is flight_count
nest : flight_legs is {
order_by : 2
group_by : [
tail_num
dep_minute is dep_time. minute
origin_code
dest_code is destination_code
dep_delay
arr_delay
]
}
}
}
}
The data styles tell the Malloy renderer how to render different kinds of results.
{
"by_carrier" : {
"renderer" : "bar_chart"
} ,
"year_over_year" : {
"renderer" : "line_chart"
} ,
"by_month" : {
"renderer" : "line_chart"
} ,
"by_manufacturer" : {
"renderer" : "bar_chart"
} ,
"routes_map" : {
"renderer" : "segment_map"
} ,
"destinations_by_month" : {
"renderer" : "line_chart"
} ,
"delay_by_hour_of_day" : {
"renderer" : "scatter_chart"
} ,
"seats_by_distance" : {
"renderer" : "scatter_chart"
} ,
"carriers_by_month" : {
"renderer" : "line_chart"
}
}